﻿using System;
using System.Collections.Specialized;
using System.ComponentModel;
using System.Configuration;
using System.Configuration.Provider;
using System.Data;
using System.Globalization;
using System.IO;
using System.Runtime.Serialization.Formatters.Binary;
using System.Text;
using System.Web.Profile;
using System.Xml.Serialization;
using MySql.Data.MySqlClient;

namespace Reviewer.Security
{
    public class ReviewerProfileProvider : ProfileProvider
    {
        #region static helper methods
        [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Design", "CA1031:DoNotCatchGeneralExceptionTypes")]
        private static void ParseDataFromDB(string[] names, string values, byte[] buf, SettingsPropertyValueCollection properties)
        {
            if (names == null || values == null || buf == null || properties == null)
                return;
            try
            {
                for (int iter = 0; iter < names.Length / 4; iter++)
                {
                    string name = names[iter * 4];
                    SettingsPropertyValue pp = properties[name];

                    if (pp == null) // property not found
                        continue;

                    int startPos = Int32.Parse(names[iter * 4 + 2], CultureInfo.InvariantCulture);
                    int length = Int32.Parse(names[iter * 4 + 3], CultureInfo.InvariantCulture);

                    if (length == -1 && !pp.Property.PropertyType.IsValueType) // Null Value
                    {
                        pp.PropertyValue = null;
                        pp.IsDirty = false;
                        pp.Deserialized = true;
                    }
                    if (names[iter * 4 + 1] == "S" && startPos >= 0 && length > 0 && values.Length >= startPos + length)
                    {
                        pp.PropertyValue = Deserialize(pp, values.Substring(startPos, length));
                    }

                    if (names[iter * 4 + 1] == "B" && startPos >= 0 && length > 0 && buf.Length >= startPos + length)
                    {
                        byte[] buf2 = new byte[length];

                        Buffer.BlockCopy(buf, startPos, buf2, 0, length);
                        pp.PropertyValue = Deserialize(pp, buf2);
                    }
                }
            }
            catch
            { // Eat exceptions
            }
        }

        private static string ConvertObjectToString(object propValue, Type type, SettingsSerializeAs serializeAs, bool throwOnError)
        {
            if (serializeAs == SettingsSerializeAs.ProviderSpecific)
            {
                if (type == typeof(string) || type.IsPrimitive)
                    serializeAs = SettingsSerializeAs.String;
                else
                    serializeAs = SettingsSerializeAs.Xml;
            }

            try
            {
                switch (serializeAs)
                {
                    case SettingsSerializeAs.String:
                        TypeConverter converter = TypeDescriptor.GetConverter(type);
                        if (converter != null && converter.CanConvertTo(typeof(String)) && converter.CanConvertFrom(typeof(String)))
                            return converter.ConvertToString(propValue);
                        throw new ArgumentException("Unable to convert type " + type.ToString() + " to string", "type");
                    case SettingsSerializeAs.Binary:
                        MemoryStream ms = new System.IO.MemoryStream();
                        try
                        {
                            BinaryFormatter bf = new BinaryFormatter();
                            bf.Serialize(ms, propValue);
                            byte[] buffer = ms.ToArray();
                            return Convert.ToBase64String(buffer);
                        }
                        finally
                        {
                            ms.Close();
                        }

                    case SettingsSerializeAs.Xml:
                        XmlSerializer xs = new XmlSerializer(type);
                        using (StringWriter sw = new StringWriter(CultureInfo.InvariantCulture))
                        {
                            xs.Serialize(sw, propValue);
                            return sw.ToString();
                        }
                }
            }
            catch (Exception)
            {
                if (throwOnError)
                    throw;
            }
            return null;
        }

        private static object SerializePropertyValue(SettingsPropertyValue prop)
        {
            object val = prop.PropertyValue;
            if (val == null)
                return null;

            if (prop.Property.SerializeAs != SettingsSerializeAs.Binary)
                return ConvertObjectToString(val, prop.Property.PropertyType, prop.Property.SerializeAs, prop.Property.ThrowOnErrorSerializing);

            MemoryStream ms = new System.IO.MemoryStream();
            try
            {
                BinaryFormatter bf = new BinaryFormatter();
                bf.Serialize(ms, val);
                return ms.ToArray();
            }
            finally
            {
                ms.Close();
            }
        }

        [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Design", "CA1031:DoNotCatchGeneralExceptionTypes"), System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Performance", "CA1800:DoNotCastUnnecessarily")]
        private static object Deserialize(SettingsPropertyValue prop, object obj)
        {
            object val = null;

            //////////////////////////////////////////////
            /// Step 1: Try creating from Serailized value
            if (obj != null)
            {
                try
                {
                    if (obj is string)
                    {
                        val = GetObjectFromString(prop.Property.PropertyType, prop.Property.SerializeAs, (string)obj);
                    }
                    else
                    {
                        MemoryStream ms = new System.IO.MemoryStream((byte[])obj);
                        try
                        {
                            val = (new BinaryFormatter()).Deserialize(ms);
                        }
                        finally
                        {
                            ms.Close();
                        }
                    }
                }
                catch
                {
                }

                if (val != null && !prop.Property.PropertyType.IsAssignableFrom(val.GetType())) // is it the correct type
                    val = null;
            }

            //////////////////////////////////////////////
            /// Step 2: Try creating from default value
            if (val == null)
            {
                if (prop.Property.DefaultValue == null || prop.Property.DefaultValue.ToString() == "[null]")
                {
                    if (prop.Property.PropertyType.IsValueType)
                        return Activator.CreateInstance(prop.Property.PropertyType);
                    else
                        return null;
                }
                if (!(prop.Property.DefaultValue is string))
                {
                    val = prop.Property.DefaultValue;
                }
                else
                {
                    try
                    {
                        val = GetObjectFromString(prop.Property.PropertyType, prop.Property.SerializeAs, (string)prop.Property.DefaultValue);
                    }
                    catch (Exception e)
                    {
                        throw new ArgumentException("Could not create from default value for property: " + prop.Property.Name, e.Message);
                    }
                }
                if (val != null && !prop.Property.PropertyType.IsAssignableFrom(val.GetType())) // is it the correct type
                    throw new ArgumentException("Could not create from default value for property: " + prop.Property.Name);
            }

            //////////////////////////////////////////////
            /// Step 3: Create a new one by calling the parameterless constructor
            if (val == null)
            {
                if (prop.Property.PropertyType == typeof(string))
                {
                    val = "";
                }
                else
                {
                    try
                    {
                        val = Activator.CreateInstance(prop.Property.PropertyType);
                    }
                    catch { }
                }
            }
            return val;
        }

        private static object GetObjectFromString(Type type, SettingsSerializeAs serializeAs, string attValue)
        {
            // Deal with string types
            if (type == typeof(string) && (attValue == null || attValue.Length < 1 || serializeAs == SettingsSerializeAs.String))
                return attValue;

            // Return null if there is nothing to convert
            if (attValue == null || attValue.Length < 1)
                return null;

            // Convert based on the serialized type
            switch (serializeAs)
            {
                case SettingsSerializeAs.Binary:
                    byte[] buf = Convert.FromBase64String(attValue);
                    MemoryStream ms = null;
                    try
                    {
                        ms = new System.IO.MemoryStream(buf);
                        return (new BinaryFormatter()).Deserialize(ms);
                    }
                    finally
                    {
                        if (ms != null)
                            ms.Close();
                    }

                case SettingsSerializeAs.Xml:
                    using (StringReader sr = new StringReader(attValue))
                    {
                        XmlSerializer xs = new XmlSerializer(type);
                        return xs.Deserialize(sr);
                    }

                case SettingsSerializeAs.String:
                    TypeConverter converter = TypeDescriptor.GetConverter(type);
                    if (converter != null && converter.CanConvertTo(typeof(String)) && converter.CanConvertFrom(typeof(String)))
                        return converter.ConvertFromString(attValue);
                    throw new ArgumentException("Unable to convert type: " + type.ToString() + " from string", "type");

                default:
                    return null;
            }
        }

        private static string GetClauseForAuthenticationOptions(ProfileAuthenticationOption authenticationOption)
        {
            switch (authenticationOption)
            {
                case ProfileAuthenticationOption.Anonymous:
                    return " AND IsAnonymous=Yes ";

                case ProfileAuthenticationOption.Authenticated:
                    return " AND IsAnonymous=No ";

                case ProfileAuthenticationOption.All:
                    return " ";
            }
            return " ";
        }

        [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Reliability", "CA2000:Dispose objects before losing scope"), System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Maintainability", "CA1502:AvoidExcessiveComplexity"), System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Performance", "CA1800:DoNotCastUnnecessarily")]
        private static void PrepareDataForSaving(ref string allNames, ref string allValues, ref byte[] buf, bool binarySupported, SettingsPropertyValueCollection properties, bool userIsAuthenticated)
        {
            StringBuilder names = new StringBuilder();
            StringBuilder values = new StringBuilder();

            using (MemoryStream ms = (binarySupported ? new System.IO.MemoryStream() : null))
            {
                try
                {
                    try
                    {
                        bool anyItemsToSave = false;

                        foreach (SettingsPropertyValue pp in properties)
                        {
                            if (pp.IsDirty)
                            {
                                if (!userIsAuthenticated)
                                {
                                    bool allowAnonymous = (bool)pp.Property.Attributes["AllowAnonymous"];
                                    if (!allowAnonymous)
                                        continue;
                                }
                                anyItemsToSave = true;
                                break;
                            }
                        }

                        if (!anyItemsToSave)
                            return;

                        foreach (SettingsPropertyValue pp in properties)
                        {
                            if (!userIsAuthenticated)
                            {
                                bool allowAnonymous = (bool)pp.Property.Attributes["AllowAnonymous"];
                                if (!allowAnonymous)
                                    continue;
                            }

                            if (!pp.IsDirty && pp.UsingDefaultValue) // Not fetched from DB and not written to
                                continue;

                            int len = 0, startPos = 0;
                            string propValue = null;

                            if (pp.Deserialized && pp.PropertyValue == null)
                            { // is value null?
                                len = -1;
                            }
                            else
                            {
                                object sVal = SerializePropertyValue(pp);

                                if (sVal == null)
                                {
                                    len = -1;
                                }
                                else
                                {
                                    if (!(sVal is string) && !binarySupported)
                                    {
                                        sVal = Convert.ToBase64String((byte[])sVal);
                                    }

                                    if (sVal is string)
                                    {
                                        propValue = (string)sVal;
                                        len = propValue.Length;
                                        startPos = values.Length;
                                    }
                                    else
                                    {
                                        byte[] b2 = (byte[])sVal;
                                        startPos = (int)ms.Position;
                                        ms.Write(b2, 0, b2.Length);
                                        ms.Position = startPos + b2.Length;
                                        len = b2.Length;
                                    }
                                }
                            }

                            names.Append(pp.Name + ":" + ((propValue != null) ? "S" : "B") +
                                         ":" + startPos.ToString(CultureInfo.InvariantCulture) + ":" + len.ToString(CultureInfo.InvariantCulture) + ":");
                            if (propValue != null)
                                values.Append(propValue);
                        }

                        if (binarySupported)
                        {
                            buf = ms.ToArray();
                        }
                    }
                    finally
                    {
                        if (ms != null)
                            ms.Close();
                    }
                }
                catch
                {
                    throw;
                }
            }

            allNames = names.ToString();
            allValues = values.ToString();
        }
        #endregion

        #region fields (private)
        private string applicationName;
        private string connectionString;
        #endregion

        /// <summary>
        /// Gets or sets the name of the currently running application.
        /// </summary>
        /// <value></value>
        /// <returns>
        /// A <see cref="T:System.String"/> that contains the application's shortened name, which does not contain a full path or extension, for example, SimpleAppSettings.
        /// </returns>
        [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Design", "CA1062:Validate arguments of public methods", MessageId = "0")]
        public override string ApplicationName
        {
            get { return this.applicationName; }
            set
            {
                if (value.Length > 255)
                    throw new ProviderException("Application name exceeded max length of " + 255);
                if (applicationName != value)
                {
                    this.applicationName = value;
                }
            }
        }

        /// <summary>
        /// Initializes the provider.
        /// </summary>
        /// <param name="name">The friendly name of the provider.</param>
        /// <param name="config">A collection of the name/value pairs representing the provider-specific attributes specified in the configuration for this provider.</param>
        /// <exception cref="T:System.ArgumentNullException">
        /// The name of the provider is null.
        /// </exception>
        /// <exception cref="T:System.ArgumentException">
        /// The name of the provider has a length of zero.
        /// </exception>
        /// <exception cref="T:System.InvalidOperationException">
        /// An attempt is made to call <see cref="M:System.Configuration.Provider.ProviderBase.Initialize(System.String,System.Collections.Specialized.NameValueCollection)"/> on a provider after the provider has already been initialized.
        /// </exception>
        public override void Initialize(string name, NameValueCollection config)
        {
            if (name == null || name.Length < 1)
            {
                name = "ReviewerProfileProvider";
            }

            if (config == null)
            {
                throw new ArgumentNullException("config");
            }

            if (string.IsNullOrEmpty(config["description"]))
            {
                config.Remove("description");
                config.Add("description", "Reviewer Profile Provider");
            }
            base.Initialize(name, config);
            if (config == null)
            {
                throw new ArgumentNullException("config");
            }

            connectionString = config["connectionStringName"];
            if (this.connectionString == null || this.connectionString.Length < 1)
                throw new ProviderException("Connection name not specified");
            string temp = ConfigurationManager.ConnectionStrings[connectionString].ConnectionString;
            if (temp == null || temp.Length < 1)
            {
                throw new ProviderException("Connection string not found: " + connectionString);
            }
            this.connectionString = temp;

            applicationName = config["applicationName"];
            if (string.IsNullOrEmpty(applicationName))
            {
                applicationName = SecUtility.GetDefaultAppName();
            }

            if (applicationName.Length > 255)
            {
                throw new ProviderException("Application name exceeded max length of " + 255);
            }

            config.Remove("connectionStringName");
            config.Remove("applicationName");
            config.Remove("description");
            if (config.Count > 0)
            {
                string attribUnrecognized = config.GetKey(0);
                if (!String.IsNullOrEmpty(attribUnrecognized))
                    throw new ProviderException("Unrecognized attribute: " + attribUnrecognized);
            }
        }

        /// <summary>
        /// Gets the property values.
        /// </summary>
        /// <param name="sc">The sc.</param>
        /// <param name="properties">The properties.</param>
        /// <returns></returns>
        public override SettingsPropertyValueCollection GetPropertyValues(SettingsContext context, SettingsPropertyCollection collection)
        {
            SettingsPropertyValueCollection svc = new SettingsPropertyValueCollection();
            if (collection == null || collection.Count < 1)
            {
                return svc;
            }

            if (context == null)
                throw new ArgumentNullException("context");

            string username = (string)context["UserName"];
            foreach (SettingsProperty prop in collection)
            {
                if (prop.SerializeAs == SettingsSerializeAs.ProviderSpecific)
                {
                    if (prop.PropertyType.IsPrimitive || prop.PropertyType == typeof(string))
                    {
                        prop.SerializeAs = SettingsSerializeAs.String;
                    }
                    else
                    {
                        prop.SerializeAs = SettingsSerializeAs.Xml;
                    }
                }

                svc.Add(new SettingsPropertyValue(prop));
            }

            if (!String.IsNullOrEmpty(username))
            {
                GetPropertyValuesFromDatabase(username, svc);
            }

            return svc;
        }

        /// <summary>
        /// Sets the property values.
        /// </summary>
        /// <param name="sc">The sc.</param>
        /// <param name="properties">The properties.</param>
        [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Design", "CA1062:Validate arguments of public methods", MessageId = "1"), System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Design", "CA1062:Validate arguments of public methods", MessageId = "0"), System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Reliability", "CA2000:Dispose objects before losing scope"), System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Design", "CA1031:DoNotCatchGeneralExceptionTypes")]
        public override void SetPropertyValues(SettingsContext context, SettingsPropertyValueCollection collection)
        {
            try
            {
                string username = (string)context["UserName"];
                bool userIsAuthenticated = (bool)context["IsAuthenticated"];
                if (username == null || username.Length < 1 || collection.Count < 1)
                {
                    return;
                }

                string names = String.Empty;
                string values = String.Empty;
                byte[] buf = null;
                PrepareDataForSaving(ref names, ref values, ref buf, false, collection, userIsAuthenticated);
                if (names.Length == 0)
                {
                    return;
                }

                ////////////////////////////////////////////////////////////
                // Step 2: Store strings in DB
                MySqlConnectionHolder holder = MySqlConnectionHelper.GetConnection(connectionString);
                MySqlCommand cmd = null;
                try
                {
                    int userId = MySqlConnectionHelper.GetUserID(holder.connectionInstance, username, true, !userIsAuthenticated);

                    if (userId == 0)
                    { // User not creatable
                        return;
                    }

                    cmd = new MySqlCommand(@"SELECT UserId FROM AN_Profile WHERE UserId = @UserId", holder.connectionInstance);
                    cmd.Parameters.Add(new MySqlParameter("@UserId", userId));
                    object result = cmd.ExecuteScalar();
                    cmd.Dispose();

                    if (result != null && (result is int) && ((int)result) == userId)
                    {
                        cmd = new MySqlCommand(@"UPDATE AN_Profile SET PropertyNames = @PropertyNames, PropertyValuesString = @PropertyValuesString, LastUpdatedDate = @LastUpdatedDate WHERE UserId = @UserId", holder.connectionInstance);
                        cmd.Parameters.Add(new MySqlParameter("@PropertyNames", names));
                        cmd.Parameters.Add(new MySqlParameter("@PropertyValuesString", values));
                        cmd.Parameters.Add(MySqlConnectionHelper.CreateDateTimeMySqlParameter("@LastUpdatedDate", DateTime.Now));
                        cmd.Parameters.Add(new MySqlParameter("@UserId", userId));
                    }
                    else
                    {
                        cmd = new MySqlCommand(@"INSERT INTO AN_Profile (UserId, PropertyNames, PropertyValuesString, LastUpdatedDate) VALUES (@UserId, @PropertyNames, @PropertyValuesString, @LastUpdatedDate)", holder.connectionInstance);
                        cmd.Parameters.Add(new MySqlParameter("@UserId", userId));
                        cmd.Parameters.Add(new MySqlParameter("@PropertyNames", names));
                        cmd.Parameters.Add(new MySqlParameter("@PropertyValuesString", values));
                        cmd.Parameters.Add(MySqlConnectionHelper.CreateDateTimeMySqlParameter("@LastUpdatedDate", DateTime.Now));
                    }
                    cmd.ExecuteNonQuery();
                    cmd.Dispose();
                    try
                    { // Not a critical part -- don't throw exceptions here
                        cmd = new MySqlCommand(@"UPDATE AN_User SET LastActivityDate=@LastActivityDate WHERE UserId = @UserId", holder.connectionInstance);
                        cmd.Parameters.Add(MySqlConnectionHelper.CreateDateTimeMySqlParameter("@LastActivityDate", DateTime.Now));
                        cmd.Parameters.Add(new MySqlParameter("@UserId", userId));
                        cmd.ExecuteNonQuery();
                        cmd.Dispose();
                    }
                    catch { }
                }
                catch (Exception e)
                {
                    throw MySqlConnectionHelper.GetBetterException(e, holder);
                }
            }
            catch
            {
                throw;
            }
        }

        /// <summary>
        /// When overridden in a derived class, deletes profile properties and information for the supplied list of profiles.
        /// </summary>
        /// <param name="profiles">A <see cref="T:System.Web.Profile.ProfileInfoCollection"/>  of information about profiles that are to be deleted.</param>
        /// <returns>
        /// The number of profiles deleted from the data source.
        /// </returns>
        public override int DeleteProfiles(ProfileInfoCollection profiles)
        {
            if (profiles == null)
            {
                throw new ArgumentNullException("profiles");
            }

            if (profiles.Count < 1)
            {
                throw new ArgumentException("Profiles collection is empty", "profiles");
            }

            foreach (ProfileInfo pi in profiles)
            {
                string username = pi.UserName;
                SecUtility.CheckParameter(ref username, true, true, true, 255, "UserName");
            }

            try
            {
                MySqlConnectionHolder holder = MySqlConnectionHelper.GetConnection(connectionString);
                int numDeleted = 0;
                try
                {
                    foreach (ProfileInfo profile in profiles)
                    {
                        if (DeleteProfile(holder, profile.UserName.Trim()))
                            numDeleted++;
                    }
                }
                catch (Exception e)
                {
                    throw MySqlConnectionHelper.GetBetterException(e, holder);
                }
                finally
                {
                    holder.Close();
                }
                return numDeleted;
            }
            catch
            {
                throw;
            }
        }

        /// <summary>
        /// When overridden in a derived class, deletes profile properties and information for profiles that match the supplied list of user names.
        /// </summary>
        /// <param name="usernames">A string array of user names for profiles to be deleted.</param>
        /// <returns>
        /// The number of profiles deleted from the data source.
        /// </returns>
        public override int DeleteProfiles(string[] usernames)
        {
            SecUtility.CheckArrayParameter(ref usernames, true, true, true, 255, "usernames");

            try
            {
                MySqlConnectionHolder holder = MySqlConnectionHelper.GetConnection(connectionString);
                int numDeleted = 0;

                try
                {
                    foreach (string username in usernames)
                    {
                        if (DeleteProfile(holder, username))
                            numDeleted++;
                    }
                }
                catch (Exception e)
                {
                    throw MySqlConnectionHelper.GetBetterException(e, holder);
                }
                finally
                {
                    holder.Close();
                }

                return numDeleted;
            }
            catch
            {
                throw;
            }
        }

        /// <summary>
        /// When overridden in a derived class, deletes all user-profile data for profiles in which the last activity date occurred before the specified date.
        /// </summary>
        /// <param name="authenticationOption">One of the <see cref="T:System.Web.Profile.ProfileAuthenticationOption"/> values, specifying whether anonymous, authenticated, or both types of profiles are deleted.</param>
        /// <param name="userInactiveSinceDate">A <see cref="T:System.DateTime"/> that identifies which user profiles are considered inactive. If the <see cref="P:System.Web.Profile.ProfileInfo.LastActivityDate"/>  value of a user profile occurs on or before this date and time, the profile is considered inactive.</param>
        /// <returns>
        /// The number of profiles deleted from the data source.
        /// </returns>
        [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Security", "CA2100:Review SQL queries for security vulnerabilities")]
        public override int DeleteInactiveProfiles(ProfileAuthenticationOption authenticationOption, DateTime userInactiveSinceDate)
        {
            try
            {
                MySqlConnectionHolder holder = MySqlConnectionHelper.GetConnection(connectionString);
                try
                {
                    string inClause = @"SELECT UserId FROM AN_User " +
                        @"WHERE LastActivityDate <= @LastActivityDate " + GetClauseForAuthenticationOptions(authenticationOption);
                    string sqlQuery = @"DELETE FROM AN_Profile WHERE UserId IN (" + inClause + ")";
                    using (MySqlCommand cmd = new MySqlCommand(sqlQuery, holder.connectionInstance))
                    {
                        cmd.Parameters.Add(MySqlConnectionHelper.CreateDateTimeMySqlParameter("@LastActivityDate", userInactiveSinceDate));
                        return cmd.ExecuteNonQuery();
                    }
                }
                catch (Exception e)
                {
                    throw MySqlConnectionHelper.GetBetterException(e, holder);
                }
                finally
                {
                    holder.Close();
                }
            }
            catch
            {
                throw;
            }

        }

        /// <summary>
        /// When overridden in a derived class, returns the number of profiles in which the last activity date occurred on or before the specified date.
        /// </summary>
        /// <param name="authenticationOption">One of the <see cref="T:System.Web.Profile.ProfileAuthenticationOption"/> values, specifying whether anonymous, authenticated, or both types of profiles are returned.</param>
        /// <param name="userInactiveSinceDate">A <see cref="T:System.DateTime"/> that identifies which user profiles are considered inactive. If the <see cref="P:System.Web.Profile.ProfileInfo.LastActivityDate"/>  of a user profile occurs on or before this date and time, the profile is considered inactive.</param>
        /// <returns>
        /// The number of profiles in which the last activity date occurred on or before the specified date.
        /// </returns>
        [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Security", "CA2100:Review SQL queries for security vulnerabilities")]
        public override int GetNumberOfInactiveProfiles(ProfileAuthenticationOption authenticationOption, DateTime userInactiveSinceDate)
        {
            try
            {
                MySqlConnectionHolder holder = MySqlConnectionHelper.GetConnection(connectionString);
                try
                {
                    string sqlQuery = @"SELECT COUNT(*) FROM AN_User u, AN_Profile p " +
                        @"WHERE LastActivityDate <= @LastActivityDate AND u.UserId = p.UserId" + GetClauseForAuthenticationOptions(authenticationOption);

                    using (MySqlCommand cmd = new MySqlCommand(sqlQuery, holder.connectionInstance))
                    {
                        cmd.Parameters.Add(MySqlConnectionHelper.CreateDateTimeMySqlParameter("@LastActivityDate", userInactiveSinceDate));
                        return (int)cmd.ExecuteScalar();
                    }
                }
                catch (Exception e)
                {
                    throw MySqlConnectionHelper.GetBetterException(e, holder);
                }
                finally
                {
                    holder.Close();
                }
            }
            catch
            {
                throw;
            }
        }

        /// <summary>
        /// When overridden in a derived class, retrieves user profile data for all profiles in the data source.
        /// </summary>
        /// <param name="authenticationOption">One of the <see cref="T:System.Web.Profile.ProfileAuthenticationOption"/> values, specifying whether anonymous, authenticated, or both types of profiles are returned.</param>
        /// <param name="pageIndex">The index of the page of results to return.</param>
        /// <param name="pageSize">The size of the page of results to return.</param>
        /// <param name="totalRecords">When this method returns, contains the total number of profiles.</param>
        /// <returns>
        /// A <see cref="T:System.Web.Profile.ProfileInfoCollection"/> containing user-profile information for all profiles in the data source.
        /// </returns>
        public override ProfileInfoCollection GetAllProfiles(ProfileAuthenticationOption authenticationOption, int pageIndex, int pageSize, out int totalRecords)
        {
            string sqlQuery = @"SELECT u.UserName, u.IsAnonymous, u.LastActivityDate, p.LastUpdatedDate, LEN(p.PropertyNames) + LEN(p.PropertyValuesString) " +
                              @"FROM AN_User u, AN_Profile p " +
                              @"WHERE u.UserId = p.UserId " +
                                    GetClauseForAuthenticationOptions(authenticationOption);
            MySqlParameter[] args = new MySqlParameter[0];
            return GetProfilesForQuery(sqlQuery, args, pageIndex, pageSize, out totalRecords);
        }

        /// <summary>
        /// When overridden in a derived class, retrieves user-profile data from the data source for profiles in which the last activity date occurred on or before the specified date.
        /// </summary>
        /// <param name="authenticationOption">One of the <see cref="T:System.Web.Profile.ProfileAuthenticationOption"/> values, specifying whether anonymous, authenticated, or both types of profiles are returned.</param>
        /// <param name="userInactiveSinceDate">A <see cref="T:System.DateTime"/> that identifies which user profiles are considered inactive. If the <see cref="P:System.Web.Profile.ProfileInfo.LastActivityDate"/>  of a user profile occurs on or before this date and time, the profile is considered inactive.</param>
        /// <param name="pageIndex">The index of the page of results to return.</param>
        /// <param name="pageSize">The size of the page of results to return.</param>
        /// <param name="totalRecords">When this method returns, contains the total number of profiles.</param>
        /// <returns>
        /// A <see cref="T:System.Web.Profile.ProfileInfoCollection"/> containing user-profile information about the inactive profiles.
        /// </returns>
        public override ProfileInfoCollection GetAllInactiveProfiles(ProfileAuthenticationOption authenticationOption, DateTime userInactiveSinceDate, int pageIndex, int pageSize, out int totalRecords)
        {
            string sqlQuery = @"SELECT u.UserName, u.IsAnonymous, u.LastActivityDate, p.LastUpdatedDate, LEN(p.PropertyNames) + LEN(p.PropertyValuesString) " +
                              @"FROM AN_User u, AN_Profile p " +
                              @"WHERE u.UserId = p.UserId AND u.LastActivityDate <= @LastActivityDate" +
                                   GetClauseForAuthenticationOptions(authenticationOption);

            MySqlParameter[] args = new MySqlParameter[1];
            args[0] = MySqlConnectionHelper.CreateDateTimeMySqlParameter("@LastActivityDate", userInactiveSinceDate);
            return GetProfilesForQuery(sqlQuery, args, pageIndex, pageSize, out totalRecords);
        }

        /// <summary>
        /// When overridden in a derived class, retrieves profile information for profiles in which the user name matches the specified user names.
        /// </summary>
        /// <param name="authenticationOption">One of the <see cref="T:System.Web.Profile.ProfileAuthenticationOption"/> values, specifying whether anonymous, authenticated, or both types of profiles are returned.</param>
        /// <param name="usernameToMatch">The user name to search for.</param>
        /// <param name="pageIndex">The index of the page of results to return.</param>
        /// <param name="pageSize">The size of the page of results to return.</param>
        /// <param name="totalRecords">When this method returns, contains the total number of profiles.</param>
        /// <returns>
        /// A <see cref="T:System.Web.Profile.ProfileInfoCollection"/> containing user-profile information for profiles where the user name matches the supplied <paramref name="usernameToMatch"/> parameter.
        /// </returns>
        public override ProfileInfoCollection FindProfilesByUserName(ProfileAuthenticationOption authenticationOption, string usernameToMatch, int pageIndex, int pageSize, out int totalRecords)
        {
            SecUtility.CheckParameter(ref usernameToMatch, true, true, false, 255, "username");

            string sqlQuery = @"SELECT u.UserName, u.IsAnonymous, u.LastActivityDate, p.LastUpdatedDate, LEN(p.PropertyNames) + LEN(p.PropertyValuesString) " +
                              @"FROM AN_User u, AN_Profile p " +
                              @"WHERE u.UserId = p.UserId AND LOWER(u.UserName) LIKE LOWER(@UserName)" +
                                  GetClauseForAuthenticationOptions(authenticationOption);
            MySqlParameter[] args = new MySqlParameter[1];
            args[0] = new MySqlParameter("@UserName", usernameToMatch);
            return GetProfilesForQuery(sqlQuery, args, pageIndex, pageSize, out totalRecords);
        }

        /// <summary>
        /// When overridden in a derived class, retrieves profile information for profiles in which the last activity date occurred on or before the specified date and the user name matches the specified user name.
        /// </summary>
        /// <param name="authenticationOption">One of the <see cref="T:System.Web.Profile.ProfileAuthenticationOption"/> values, specifying whether anonymous, authenticated, or both types of profiles are returned.</param>
        /// <param name="usernameToMatch">The user name to search for.</param>
        /// <param name="userInactiveSinceDate">A <see cref="T:System.DateTime"/> that identifies which user profiles are considered inactive. If the <see cref="P:System.Web.Profile.ProfileInfo.LastActivityDate"/> value of a user profile occurs on or before this date and time, the profile is considered inactive.</param>
        /// <param name="pageIndex">The index of the page of results to return.</param>
        /// <param name="pageSize">The size of the page of results to return.</param>
        /// <param name="totalRecords">When this method returns, contains the total number of profiles.</param>
        /// <returns>
        /// A <see cref="T:System.Web.Profile.ProfileInfoCollection"/> containing user profile information for inactive profiles where the user name matches the supplied <paramref name="usernameToMatch"/> parameter.
        /// </returns>
        public override ProfileInfoCollection FindInactiveProfilesByUserName(ProfileAuthenticationOption authenticationOption, string usernameToMatch, DateTime userInactiveSinceDate, int pageIndex, int pageSize, out int totalRecords)
        {
            SecUtility.CheckParameter(ref usernameToMatch, true, true, false, 255, "usernameToMatch");
            string sqlQuery = @"SELECT u.UserName, u.IsAnonymous, u.LastActivityDate, p.LastUpdatedDate, LEN(p.PropertyNames) + LEN(p.PropertyValuesString) " +
                              @"FROM AN_User u, AN_Profile p " +
                              @"WHERE u.UserId = p.UserId AND LOWER(u.UserName) like LOWER(@UserName) AND u.LastActivityDate <= @LastActivityDate" +
                                     GetClauseForAuthenticationOptions(authenticationOption);
            MySqlParameter[] args = new MySqlParameter[2];
            args[0] = new MySqlParameter("@UserName", usernameToMatch);
            args[1] = MySqlConnectionHelper.CreateDateTimeMySqlParameter("@LastActivityDate", userInactiveSinceDate);
            return GetProfilesForQuery(sqlQuery, args, pageIndex, pageSize, out totalRecords);
        }

        #region methods (private)
        [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Design", "CA1031:DoNotCatchGeneralExceptionTypes")]
        private void GetPropertyValuesFromDatabase(string username, SettingsPropertyValueCollection svc)
        {
            try
            {
                MySqlConnectionHolder holder = MySqlConnectionHelper.GetConnection(connectionString);
                string[] names = null;
                string values = null;
                MySqlDataReader reader = null;
                ////////////////////////////////////////////////////////////
                // Step 1: Get Values from DB
                try
                {
                    int userId = MySqlConnectionHelper.GetUserID(holder.connectionInstance, username, false);

                    if (userId != 0)
                    { // User exists?
                        using (MySqlCommand cmd = new MySqlCommand(@"SELECT PropertyNames, PropertyValuesString " +
                                                            @"FROM AN_Profile " +
                                                            @"WHERE UserId = @UserId",
                                                            holder.connectionInstance))
                        {
                            cmd.Parameters.Add(new MySqlParameter("@UserId", userId));
                            reader = cmd.ExecuteReader();
                            if (reader.Read())
                            {
                                names = reader.GetString(0).Split(':');
                                values = reader.GetString(1);
                            }
                        }

                        try
                        { // Not a critical part -- don't throw exceptions here
                            using (MySqlCommand cmd = new MySqlCommand(@"UPDATE AN_User SET LastActivityDate=@LastActivityDate WHERE UserId = @UserId", holder.connectionInstance))
                            {
                                cmd.Parameters.Add(MySqlConnectionHelper.CreateDateTimeMySqlParameter("@LastActivityDate", DateTime.Now));
                                cmd.Parameters.Add(new MySqlParameter("@UserId", userId));
                                cmd.ExecuteNonQuery();
                            }
                        }
                        catch { }
                    }
                }
                catch (Exception e)
                {
                    throw MySqlConnectionHelper.GetBetterException(e, holder);
                }
                finally
                {
                    if (reader != null)
                        reader.Close();
                    holder.Close();
                }
                if (names != null && names.Length > 0)
                {
                    ParseDataFromDB(names, values, new byte[0], svc);
                }
            }
            catch
            {
                throw;
            }
        }

        private ProfileInfoCollection GetProfilesForQuery(string sqlQuery, MySqlParameter[] args, int pageIndex, int pageSize, out int totalRecords)
        {
            if (pageIndex < 0)
                throw new ArgumentException("Page index must be non-negative", "pageIndex");
            if (pageSize < 1)
                throw new ArgumentException("Page size must be positive", "pageSize");

            long lBound = (long)pageIndex * pageSize;
            long uBound = lBound + pageSize - 1;

            if (uBound > System.Int32.MaxValue)
            {
                throw new ArgumentException("pageIndex*pageSize too large");
            }
            try
            {
                MySqlConnectionHolder holder = MySqlConnectionHelper.GetConnection(connectionString);
                ProfileInfoCollection profiles = new ProfileInfoCollection();
                MySqlDataReader reader = null;
                try
                {
                    using (MySqlCommand cmd = new MySqlCommand(sqlQuery, holder.connectionInstance))
                    {
                        int len = args.Length;
                        for (int iter = 0; iter < len; iter++)
                            cmd.Parameters.Add(args[iter]);
                        reader = cmd.ExecuteReader(CommandBehavior.SequentialAccess);
                        totalRecords = 0;
                        while (reader.Read())
                        {
                            totalRecords++;
                            if (totalRecords - 1 < lBound || totalRecords - 1 > uBound)
                                continue;

                            string username;
                            DateTime dtLastActivity, dtLastUpdated;
                            bool isAnon;

                            username = reader.GetString(0);
                            isAnon = reader.GetBoolean(1);
                            dtLastActivity = reader.GetDateTime(2);
                            dtLastUpdated = reader.GetDateTime(3);
                            int size = reader.GetInt32(4);
                            profiles.Add(new ProfileInfo(username, isAnon, dtLastActivity, dtLastUpdated, size));
                        }
                        return profiles;
                    }
                }
                catch (Exception e)
                {
                    throw MySqlConnectionHelper.GetBetterException(e, holder);
                }
                finally
                {
                    if (reader != null)
                        reader.Close();
                    holder.Close();
                }
            }
            catch
            {
                throw;
            }

        }

        private static bool DeleteProfile(MySqlConnectionHolder holder, string username)
        {
            SecUtility.CheckParameter(ref username, true, true, true, 255, "username");

            int userId = MySqlConnectionHelper.GetUserID(holder.connectionInstance, username, false);
            if (userId == 0)
                return false;
            using (MySqlCommand cmd = new MySqlCommand(@"DELETE FROM AN_Profile WHERE UserId = @UserId", holder.connectionInstance))
            {
                cmd.Parameters.Add(new MySqlParameter("@UserId", userId));
                return (cmd.ExecuteNonQuery() != 0);
            }
        }

        //private MySqlParameter CreateDateTimeMySqlParameter(string parameterName, DateTime dt)
        //{
        //    MySqlParameter p = new MySqlParameter(parameterName, MySqlDbType.DateTime);
        //    p.Direction = ParameterDirection.Input;
        //    p.Value = MySqlConnectionHelper.RoundToSeconds(dt);
        //    return p;
        //}
        #endregion

    }
}